﻿Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Text

Public NotInheritable Class DAL
    Implements IDisposable

    Private sqlCn As New SqlConnection()
    Private _LastException As SqlException
    Public ReadOnly Property LastException() As SqlException
        Get
            Return _LastException
        End Get
    End Property

    Public Event Feedback(ByVal message As String)

    Public Sub New(ByVal cnnString As String)
        MyBase.New()
        Me.OpenConnection(cnnString)
    End Sub

    Public Overrides Function ToString() As String
        If Me.sqlCn Is Nothing Then
            Return ""
        Else
            Return Me.sqlCn.ToString
        End If
    End Function

    Private Sub OpenConnection(ByVal cnnString As String)
        If sqlCn.State <> ConnectionState.Open Then
            Try
                If Me.sqlCn Is Nothing Then
                    Me.sqlCn = New SqlConnection
                End If
                sqlCn.ConnectionString = cnnString
                sqlCn.Open()
            Catch sqlEx As SqlException
                Me._LastException = sqlEx
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub CloseConnection()
        If sqlCn.State = ConnectionState.Open Then
            sqlCn.Close()
        End If

        Me.Dispose()
    End Sub

    Public Function GetDataTable(ByVal SQL As String) As DataTable
        Dim inv As New DataTable()

        Try
            Using cmd As New SqlCommand(SQL, Me.sqlCn)
                Dim dr As SqlDataReader = cmd.ExecuteReader()
                inv.Load(dr)
                dr.Close()
            End Using
            Me.CloseConnection()
            Return inv
        Catch sqlEx As SqlException
            Return Nothing
        Catch ex As Exception
            Return Nothing
        End Try

    End Function

    Public Function GetMultipleDataTables(ByVal SQL As String) As Generic.SortedList(Of Integer, DataTable)
        Dim Datasets As New Generic.SortedList(Of Integer, DataTable)

        Try
            Using cmd As New SqlCommand(SQL, Me.sqlCn)
                cmd.CommandTimeout = 0
                Dim dr As SqlDataReader = cmd.ExecuteReader()
                Dim nextResult As Boolean = True
                Dim i As Integer = 0

                Do Until Not nextResult
                    Dim dt As New DataTable
                    dt.Load(dr)
                    Datasets.Add(i, dt)
                    i += 1
                    RaiseEvent Feedback(Strings.Format("Dataset #" & i.ToString & " has been added."))
                    If dr.IsClosed Then
                        nextResult = False
                    End If
                Loop

                dr.Close()

            End Using
            Me.CloseConnection()
            Return Datasets
        Catch sqlEx As SqlException
            Return Nothing
        Catch ex As Exception
            Return Nothing
        End Try

    End Function

    Public Function RunNonQuery(ByVal SQL As String) As Object
        Try
            If sqlCn.State = ConnectionState.Closed Then
                sqlCn.Open()
            End If
            Using cmd As New SqlCommand(SQL, Me.sqlCn)
                cmd.CommandTimeout = 0
                cmd.ExecuteNonQuery()
            End Using
        Catch sqlEx As SqlException
            Return sqlEx
        Catch ex As Exception
            Return ex
        End Try

        Me.CloseConnection()
        Return Nothing
    End Function

    Public Function ExecuteProc(ByVal SP As String, ByVal params As Array) As Object
        Dim SQL As New StringBuilder
        SQL.Append("EXEC dbo.")
        SQL.Append(SP)
        SQL.Append(" ")

        For index As Integer = 0 To params.Length - 1
            SQL.Append(params(index))
            SQL.Append(", ")
        Next

        SQL.Remove(SQL.Length - 2, 2)

        Try
            If sqlCn.State = ConnectionState.Closed Then
                sqlCn.Open()
            End If
            Using cmd As New SqlCommand(SQL.ToString, Me.sqlCn)
                cmd.CommandTimeout = 0
                cmd.ExecuteNonQuery()
            End Using
        Catch sqlEx As SqlException
            Return sqlEx
        Catch ex As Exception
            Return ex.ToString
        End Try

        Me.CloseConnection()
        Return Nothing
    End Function

    Public Function PerformTransaction(ByVal OrderedCmdList As Generic.SortedList(Of Integer, String)) As Boolean
        Dim trans = sqlCn.BeginTransaction()
        Try
            For i As Integer = 0 To OrderedCmdList.Count - 1
                Using cmd As New SqlCommand(OrderedCmdList(i), Me.sqlCn)
                    cmd.Transaction = trans
                    cmd.CommandTimeout = 0
                    cmd.ExecuteNonQuery()
                End Using
            Next

            trans.Commit()

        Catch sqlEx As SqlException
            Me._LastException = sqlEx
            trans.Rollback()
            Return False
        End Try

        Me.CloseConnection()
        Return True
    End Function

    Public Function PerformBulkCommands(ByVal OrderedCmdList As Generic.SortedList(Of Integer, String), ByVal IgnoreErrors As Boolean) As Object
        For i As Integer = 0 To OrderedCmdList.Count - 1
            Try
                Using cmd As New SqlCommand(OrderedCmdList(i), Me.sqlCn)
                    cmd.CommandTimeout = 0
                    cmd.ExecuteNonQuery()
                End Using

            Catch sqlEx As SqlException
                Me._LastException = sqlEx
                If IgnoreErrors = False Then
                    Exit For
                    Return sqlEx
                End If
            End Try
        Next

        Me.CloseConnection()
        Return Nothing
    End Function

    Public Sub Dispose() Implements IDisposable.Dispose
        Me.sqlCn = Nothing
        GC.Collect()
    End Sub
End Class
